<?php

namespace SLN0002\DAO;

use Home\DAO\BizConfigDAO;
use Home\DAO\PSIBaseExDAO;

/**
 * 记账 DAO
 *
 * @author PSI
 * @copyright 2015 - present
 * @license GPL v3
 */
class KaDAO extends PSIBaseExDAO
{
  /**
   * 检查凭证号是否连续
   */
  private function checkVoucherRef($params)
  {
    $db = $this->db;

    // 当前会计期间
    $year = $params["year"];
    $month = $params["month"];

    $companyId = $params["companyId"];

    // 查询凭证字
    $sql = "select name
            from t_sln0002_ct_voucher_word
            where company_id = '%s'
            order by code";
    $data = $db->query($sql, $companyId);
    $wordList = [];
    foreach ($data as $v) {
      $wordList[] = $v["name"];
    }
    if (count($wordList) == 0) {
      // 没有启用凭证字
      $wordList[] = "";
    }

    $break = false;
    foreach ($wordList as $word) {
      $sql = "select v_ref
              from t_voucher
              where company_id = '%s' and v_word = '%s'
                and v_year = %d and v_month = %d
              order by v_ref";
      $data = $db->query($sql, $companyId, $word, $year, $month);
      foreach ($data as $i => $v) {
        $ref = $v["v_ref"] - 1;
        if ($i != $ref) {
          $break = true;
          break;
        }
      }
    }

    return $break ? $this->bad("凭证存在断号，凭证断号重排处理后才能记账") : null;
  }

  /**
   * 登记明细账
   */
  public function keepDetail(&$params)
  {
    $db = $this->db;

    $companyId = $params["companyId"];
    $ignoreCheck = $params["ignoreCheck"];

    // 检查companyId是否存在
    $sql = "select name from t_org where id = '%s' and parent_id is null";
    $data = $db->query($sql, $companyId);
    if (!$data) {
      return $this->badParam("companyId");
    }

    $companyName = $data[0]["name"];

    // 1. 查询当前会计期间
    $sql = "select acc_year, acc_month
            from t_acc_current_period
            where company_id = '%s' ";
    $data = $db->query($sql, $companyId);
    if (!$data) {
      // 莫名的bug
      return $this->badParam("companyId");
    }

    $currentYear = $data[0]["acc_year"];
    $currentMonth = $data[0]["acc_month"];

    // 检查当前会计期间是否已经关账
    $sql = "select acc_detail_closed
            from t_acc_period
            where company_id = '%s' and acc_year = %d and acc_month = %d";
    $data = $db->query($sql, $companyId, $currentYear, $currentMonth);
    if (!$data) {
      // 莫名的bug
      return $this->bad("没有初始化会计期间");
    }
    $detailClosed = $data[0]["acc_detail_closed"];
    if ($detailClosed == 1) {
      return $this->bad("明细分类账已经关账，不能再进行记账操作");
    }

    // 2. 查询当前会计期间内的未记账的凭证列表
    $sql = "select id
            from t_voucher
            where company_id = '%s' and v_status = 1000
              and v_year = %d and v_month = %d
            order by v_word, v_ref";
    $data = $db->query($sql, $companyId, $currentYear, $currentMonth);
    $cntVoucher = count($data);

    if (!$ignoreCheck) {
      if ($cntVoucher === 0) {
        return $this->bad("所有的凭证都已经记账了");
      }
    }

    // 3. 检查是否有断号凭证
    $p = [
      "year" => $currentYear,
      "month" => $currentMonth,
      "companyId" => $companyId,
    ];
    $rc = $this->checkVoucherRef($p);
    if ($rc) {
      return $rc;
    }

    // 4. 循环凭证列表，完成记账操作
    foreach ($data as $v) {
      $voucherId = $v["id"];

      $paramsOne = [
        "id" => $voucherId,
        "companyId" => $companyId,
        "loginUserId" => $params["loginUserId"],
      ];

      $rc = $this->keepDetailFromOneVoucher($paramsOne);
      if ($rc) {
        return $rc;
      }
    }

    // 业务日志
    $log = "[{$companyName}] - 明细账记账操作成功，共有{$cntVoucher}张凭证完成记账";
    $params["log"] = $log;

    // 操作成功
    return null;
  }


  /**
   * 把一张凭证的分录登记明细账
   */
  public function keepDetailFromOneVoucher(&$params)
  {
    $db = $this->db;

    // 凭证id
    $id = $params["id"];

    $loginUserId = $params["loginUserId"];
    $sql = "select name
            from t_user
            where id = '%s' ";
    $data = $db->query($sql, $loginUserId);
    if (!$data) {
      return $this->badParam("loginUserId");
    }
    $loginUserName = $data[0]["name"];

    $companyId = $params["companyId"];

    // 检查companyId
    $sql = "select name 
            from t_org 
            where id = '%s' and parent_id is null";
    $data = $db->query($sql, $companyId);
    if (!$data) {
      return $this->badParam("companyId");
    }
    $companyName = $data[0]["name"];

    $sql = "select v_status, ref, v_year, v_month, biz_user_id, biz_user_name,
              charge_user_id, charge_user_name, v_dt, v_word, v_ref
            from t_voucher
            where id = '%s' ";
    $data = $db->query($sql, $id);
    if (!$data) {
      return $this->bad("记账凭证不存在");
    }
    $v = $data[0];
    $voucherStatus = $v["v_status"];
    $ref = $v["ref"];
    $voucherDT = $v["v_dt"];
    $voucherYear = $v["v_year"];
    $voucherMonth = $v["v_month"];
    $voucherWord = $v["v_word"];
    $voucherRef = $v["v_ref"];
    $accUserId = $v["charge_user_id"];
    $accUserName = $v["charge_user_name"];
    $bizUserId = $v["biz_user_id"];
    $bizUserName = $v["biz_user_name"];

    // 检查凭证状态
    if ($voucherStatus < 1000) {
      return $this->bad("凭证号为{$ref}的凭证还没有复核，不能记账");
    } elseif ($voucherStatus < 1000) {
      return $this->bad("凭证号为{$ref}的凭证已经记账了，不能重复记账");
    }

    // 检查要记账的凭证是否是在当前会计期间内
    $daoComm = new AccCommDAO($db);
    $r = $daoComm->getCurrentAccYearAndMonth($companyId);
    if (!$r) {
      // 莫名的bug
      return $this->bad("没有设置[{$companyName}]当前会计期间");
    }

    $accYear = $r["accYear"];
    $accMonth = $r["accMonth"];
    if ($voucherYear !== $accYear || $voucherMonth !== $accMonth) {
      return $this->bad("{$ref}号凭证的日期不在当前会计期间内，不能记账");
    }

    // 检查凭证借贷是否平衡
    $sql = "select sum(debit) as sum_db, sum(credit) as sum_cr
            from t_voucher_detail
            where voucher_id = '%s' ";
    $data = $db->query($sql, $id);
    $sumDb = $data[0]["sum_db"];
    $sumCr = $data[0]["sum_cr"];
    if (abs($sumDb - $sumCr) > 0.0001) {
      return $this->bad("{$ref}号的凭证借贷不平，不能记账");
    }

    $daoComm = new AccCommDAO($db);

    // 查询凭证分录明细
    $sql = "select id, subject, debit, credit, summary
            from t_voucher_detail
            where voucher_id = '%s' 
            order by show_order ";
    $data = $db->query($sql, $id);

    foreach ($data as $i => $v) {
      $index = $i + 1;

      $voucherDetailId = $v["id"];
      $subjectCode = $v["subject"];
      $debit = $v["debit"];
      $credit = $v["credit"];
      $isDebit = $debit !== null;
      $summary = $v["summary"];

      // 查询账样
      $sql = "select id, db_table_name_prefix, voucher_db_table_name
              from t_acc_fmt
              where company_id = '%s' and subject_code = '%s' ";
      $d = $db->query($sql, $companyId, $subjectCode);
      if (!$d) {
        //  莫名的bug
        return $this->bad("账样不存在");
      }

      $fmtId = $d[0]["id"];
      $voucherTableNameEx = $d[0]["voucher_db_table_name"];
      $accTableName = $d[0]["db_table_name_prefix"];

      // 查询账样列
      $sql = "select db_field_name, voucher_input
              from t_acc_fmt_cols
              where fmt_id = '%s' and sys_col = 0 and col_category != 1";
      $fmtCols = $db->query($sql, $fmtId);

      // 查询子账簿层级字段
      // 目前的设计是：最多有三个层级字段
      $sql = "select db_field_name, voucher_input, sub_acc_level
              from t_acc_fmt_cols
              where fmt_id = '%s' and sub_acc_level > 0 and sub_acc_level < 4
              order by sub_acc_level desc
              limit 3";
      $dataSub = $db->query($sql, $fmtId);
      $subLevelFields = [];
      foreach ($dataSub as $vSub) {
        $subLevelFields[] = [
          "fieldName" => $vSub["db_field_name"],
          "voucherInput" => $vSub["voucher_input"],
          "subAccLevel" => $vSub["sub_acc_level"],
        ];
      }
      $subAccLevel = count($subLevelFields);
      $subLevelFieldsAscOrder = array_reverse($subLevelFields);

      $indexCMP = 0;
      $listCMP = ["x_v_cnt", "x_db_cnt", "x_db_price", "x_cr_cnt", "x_cr_price", "x_balance_cnt", "x_balance_price"];
      foreach ($listCMP as $fieldCMP) {
        $sql = "select db_field_name
                from t_acc_fmt_cols
                where fmt_id = '%s' and db_field_name = '%s' ";
        $data = $db->query($sql, $fmtId, $fieldCMP);
        if ($data) {
          $indexCMP++;
        }
      }
      $isCMP = $indexCMP == count($listCMP);

      // 查询分录附加项数据
      $sql = "select * from {$voucherTableNameEx}
              where id = '%s' ";
      $dataEx = $db->query($sql, $voucherDetailId);
      if (count($dataEx) != 1) {
        // 莫名的bug
        return $this->bad("分录附件数据异常");
      }
      $dataExRecord = $dataEx[0];
      $recordForSql = [];
      foreach ($fmtCols as $c) {
        $fieldName = $c["db_field_name"];
        $voucherInput = $c["voucher_input"];
        if ($voucherInput == 2) {
          // 码表录入
          // 这时候$fieldName对应的字段里面保存的是码表记录的编码
          $fName = $fieldName . "_name"; // 码表记录的name数据
          $fieldId = $fieldName . "_id"; // 码表记录的id

          $recordForSql[] = [
            "fieldName" => $fieldName,
            "fieldValue" => $dataExRecord[$fieldName],
          ];
          $recordForSql[] = [
            "fieldName" => $fName,
            "fieldValue" => $dataExRecord[$fName],
          ];
          $recordForSql[] = [
            "fieldName" => $fieldId,
            "fieldValue" => $dataExRecord[$fieldId],
          ];
        } elseif ($voucherInput == 1) {
          // 直接录入
          $recordForSql[] = [
            "fieldName" => $fieldName,
            "fieldValue" => $dataExRecord[$fieldName],
          ];
        } else {
          // 莫名的bug
          return $this->bad("无效的voucher_input数据");
        }
      }

      $accTableNameDetail = $accTableName . "_detail";

      // 查询科目余额方向
      $r = $daoComm->querySubjectDir($companyId, $subjectCode);
      if (!$r) {
        // 莫名的bug
        return $this->badParam("subjectCode");
      }
      $accBalanceDbCr = $r;

      // 循环处理子账簿
      // 对于没有子账簿的，本循环会只执行一次，即$subAccLevelIndex = 0
      for ($subAccLevelIndex = 0; $subAccLevelIndex <= $subAccLevel; $subAccLevelIndex++) {
        // 目前的设计：子账簿只有三层
        if ($subAccLevelIndex > 3) {
          break;
        }

        $currentSubAccLevel = $subAccLevelIndex;
        if ($currentSubAccLevel === 0) {
          $currentSubAccLevel = -1;
        }

        // 查询明细账中最后一条记录，用于计算余额
        $queryParamsForBalance = [];
        $sql = "";
        if ($isCMP) {
          $sql = "select x_balance_cnt, acc_balance, acc_balance_dbcr
                from {$accTableNameDetail}
                where 1 = 1 ";
        } else {
          $sql = "select acc_balance, acc_balance_dbcr
                from {$accTableNameDetail}
                where 1 = 1 ";
        }

        for ($idx = 0; $idx < $currentSubAccLevel; $idx++) {
          $sub = $subLevelFieldsAscOrder[$idx];
          $fn = $sub["fieldName"];

          if ($sub["voucherInput"] == 2) {
            $fn .= "_id";
          }

          $sql .= " and {$fn} = '%s' ";
          // 值
          foreach ($recordForSql as $r) {
            $f = $r["fieldName"];
            if ($f == $fn) {
              $queryParamsForBalance[] = $r["fieldValue"];
              break;
            }
          }
        }
        $sql .= " and sub_acc_level = %d
                  order by id desc
                  limit 1";
        $queryParamsForBalance[] = $currentSubAccLevel;
        $d = $db->query($sql, $queryParamsForBalance);
        $accBalance = 0;
        $lastBalanceCnt = 0;
        $lastBalanceDir = "借方";
        if ($d) {
          $accBalance = $d[0]["acc_balance"];
          $lastBalanceDir = $d[0]["acc_balance_dbcr"];

          $lastBalanceCnt = $d[0]["x_balance_cnt"];
        }

        // 构建插入明细账数据的SQL
        $sql = "insert into {$accTableNameDetail} (";

        // 账样标准列
        $sql .= "acc_id, company_id, acc_user_id, acc_user_name, biz_user_id, biz_user_name, 
                  subject_code, voucher_dt, voucher_year, voucher_month, voucher_word,
                  voucher_number, je_number, acc_db, acc_cr, acc_balance, acc_balance_dbcr,
                  voucher_summary, sub_acc_level";
        $valueStat = "'%s', '%s', '%s', '%s', '%s', '%s',
                      '%s', '%s', %d, %d, '%s',
                      %d, %d, if(abs(%f)<0.0000001, null, %f), if(abs(%f)<0.0000001, null, %f),
                      %f, '%s', '%s', %d";

        // 插入的数据
        $curentAccId = $this->newId();
        $insertParams = [
          $curentAccId,
          $companyId,
          $accUserId,
          $accUserName,
          $bizUserId,
          $bizUserName,
          $subjectCode,
          $voucherDT,
          $voucherYear,
          $voucherMonth,
          $voucherWord,
          $voucherRef,
          $index, // 分录号
          $debit,
          $debit,
          $credit,
          $credit,
        ];

        // 计算余额和余额方向
        // =====================================================
        // 会计原理是这样的
        // 资产 + 费用 = 负债 + 所有者权益 + 收入
        // 等式左边（借方+，贷方-）  等式右边（借方-， 贷方+）
        // ------------------------------------------------------
        $dirName = $accBalanceDbCr;
        if ($accBalanceDbCr == "借方") {
          if ($isDebit) {
            // 数据在借方，用加法
            $accBalance += $debit;
          } else {
            // 数据在贷方，用减法
            $accBalance -= $credit;
          }
        } elseif ($accBalanceDbCr == "贷方") {
          if ($isDebit) {
            // 数据在借方，用减法
            $accBalance -= $debit;
          } else {
            // 数据在贷方，用加法
            $accBalance += $credit;
          }
        } elseif ($accBalanceDbCr == "借贷均可") {
          // 例如：科目4103本年利润，余额是借贷均可
          // 这时候余额方向在账里面到底是记录借方还是贷方的算法是
          // 先取得余额，借方余额为原数，借方余额为相反数
          // 余额 + 借方 - 贷方 = 新余额，新余额>0为借方，新余额 < 0 为借方，记账的时候余额取绝对值
          if ($lastBalanceDir == "贷方") {
            $accBalance = -$accBalance;
          }
          if ($isDebit) {
            $accBalance += $debit;
          } else {
            $accBalance -= $credit;
          }
          if ($accBalance < 0) {
            $dirName = "贷方";
          } else {
            $dirName = "借方";
          }
          $accBalance = abs($accBalance);
        } else {
          // 莫名的bug
          return $this->badParam("accBalanceDbCr");
        }

        $insertParams[] = $accBalance;
        $insertParams[] = $dirName;
        $insertParams[] = $summary;
        $insertParams[] = $currentSubAccLevel;

        // 根据子账簿级数生成忽略字段
        // 被忽略的字段就不会插入数据
        $ignorSubAccFields = [];
        foreach ($subLevelFields as $slf) {
          if ($slf["subAccLevel"] > $currentSubAccLevel) {
            $ignorSubAccFields[] = $slf["fieldName"];
          }
        }

        // 账样扩展列
        foreach ($fmtCols as $c) {
          $fieldName = $c["db_field_name"];
          if (in_array($fieldName, $ignorSubAccFields)) {
            // 当前字段是子账簿忽略字段，就跳转到下一个账样扩展列
            continue;
          }

          $sql .= ", ";

          $voucherInput = $c["voucher_input"];
          if ($voucherInput == 2) {
            // 码表录入
            // 这时候$fieldName对应的字段里面保存的是码表记录的编码
            $fName = $fieldName . "_name"; // 码表记录的name数据
            $fieldId = $fieldName . "_id"; // 码表记录的id

            $sql .= "{$fieldName}, {$fName}, {$fieldId}";

            $valueStat .= ", '%s', '%s', '%s'";

            // 值
            $fv1 = "";
            $fv2 = "";
            $fv3 = "";
            foreach ($recordForSql as $r) {
              $f = $r["fieldName"];
              if ($f == $fieldName) {
                $fv1 = $r["fieldValue"];
              } elseif ($f == $fName) {
                $fv2 = $r["fieldValue"];
              } elseif ($f == $fieldId) {
                $fv3 = $r["fieldValue"];
              }
            }
            $insertParams[] = $fv1;
            $insertParams[] = $fv2;
            $insertParams[] = $fv3;
          } elseif ($voucherInput == 1) {
            // 直接录入
            $sql .= $fieldName;
            $valueStat .= ", '%s'";

            // 值
            foreach ($recordForSql as $r) {
              $f = $r["fieldName"];
              if ($f == $fieldName) {
                $insertParams[] = $r["fieldValue"];
                break;
              }
            }
          } else {
            // 莫名的bug
            return $this->bad("无效的voucher_input数据");
          }
        }
        $sql .= ") values ({$valueStat})";

        // 执行插入SQL
        $rc = $db->execute($sql, $insertParams);
        if ($rc === false) {
          return $this->sqlError(__METHOD__, __LINE__);
        }

        // 有子账簿，并且是最明细的一个子账簿
        if ($subAccLevelIndex == $subAccLevel && $subAccLevelIndex > 0) {
          // 如果是数量金额三栏账，需要同步更新数量和单价
          if ($isCMP) {
            // 由凭证录入的数量
            $c = floatval($dataExRecord["x_v_cnt"]);

            // 借方或贷方的数量和单价
            $m = 0;
            if ($isDebit) {
              $m = $debit;
            } else {
              $m = $credit;
            }
            $p = 0;
            if (abs($c) < 0.0000001) {
              // 防止除零
              $p = 0;
            } else {
              $p = $m / $c;
            }

            // 余额的数量和单价
            $m2 = $accBalance;
            $c2 = 0;
            if ($isDebit) {
              $c2 = $lastBalanceCnt + $c;
            } else {
              $c2 = $lastBalanceCnt - $c;
            }
            $p2 = 0;
            if (abs($c2) < 0.0000001) {
              // 防止除零
              $p2 = 0;
            } else {
              $p2 = $m2 / $c2;
            }

            $sql = "";
            if ($isDebit) {
              $sql = "update {$accTableNameDetail}
                      set x_db_cnt = %f, x_db_price = %f,
                        x_cr_cnt = null, x_cr_price = null,
                        x_balance_cnt = %f, x_balance_price = %f
                      where acc_id = '%s' ";
            } else {
              $sql = "update {$accTableNameDetail}
                      set x_cr_cnt = %f, x_cr_price = %f,
                        x_db_cnt = null, x_db_price = null,
                        x_balance_cnt = %f, x_balance_price = %f
                      where acc_id = '%s' ";
            }
            $rc = $db->execute($sql, $c, $p, $c2, $p2, $curentAccId);
            if ($rc === false) {
              return $this->sqlError(__METHOD__, __LINE__);
            }
          }
        }
      } // for - 循环处理子账簿
    } // end of foreach - 凭证分录明细

    // 修改凭证状态为：已记明细账
    $sql = "update t_voucher
            set v_status = 2000,
              gl_user_id = '%s', gl_user_name = '%s', gl_dt = now()
            where id = '%s' ";
    $rc = $db->execute($sql, $loginUserId, $loginUserName, $id);
    if ($rc === false) {
      return $this->sqlError(__METHOD__, __LINE__);
    }
    $sql = "update t_voucher_detail
            set gl_flag = 1
            where voucher_id = '%s' ";
    $rc = $db->execute($sql, $id);
    if ($rc === false) {
      return $this->sqlError(__METHOD__, __LINE__);
    }

    // 业务日志
    $log = "{$companyName} - 完成明细账记账操作，凭证号为{$ref}";
    $params["log"] = $log;

    // 操作成功
    return null;
  }

  /**
   * 查询明细账簿列表
   */
  public function queryAccList($params)
  {
    $db = $this->db;

    $companyId = $params["companyId"];
    $subjectCode = $params["subjectCode"];

    $queryParams = [$companyId];
    $sql = "select id, acc_number, subject_code
            from t_acc_fmt
            where company_id = '%s' and in_use = 1";

    if ($subjectCode) {
      $sql .= " and subject_code like '%s' ";
      $queryParams[] = "%{$subjectCode}%";
    }

    $sql .= " order by acc_number";
    $data = $db->query($sql, $queryParams);

    $result = [];

    foreach ($data as $v) {
      $fmtId = $v["id"];
      $subjectCode = $v["subject_code"];

      $sql = "select count(db_field_name) as cnt
              from t_acc_fmt_cols
              where fmt_id = '%s' and sub_acc_level between 1 and 3";
      $d = $db->query($sql, $fmtId);
      $cnt = $d[0]["cnt"];
      if ($cnt === 0) {
        $cnt = -1;
      }

      $sql = "select name
              from t_subject
              where code = '%s' ";
      $d = $db->query($sql, $subjectCode);
      $name = $d[0]["name"];

      $result[] = [
        "id" => $fmtId,
        "accNumber" => $v["acc_number"],
        "name" => $name,
        "subjectCode" => $subjectCode,
        "subAccLevel" => $cnt,
      ];
    }

    return $result;
  }

  /**
   * 查询明细账的账样列
   */
  public function queryFmtColsForDetailAcc($params)
  {
    $db = $this->db;

    $companyId = $params["companyId"];
    $subjectCode = $params["subjectCode"];

    $result = [];

    $sql = "select id 
            from t_acc_fmt 
            where company_id = '%s' and subject_code = '%s' ";
    $data = $db->query($sql, $companyId, $subjectCode);
    if (!$data) {
      return $result;
    }

    $fmtId = $data[0]["id"];

    $ignorFields = [];
    $sql = "select id, caption, db_field_name, col_width
            from t_acc_fmt_cols
            where fmt_id = '%s' and show_order > 0 and col_category != 1
            order by show_order ";
    $data = $db->query($sql, $fmtId);
    foreach ($data as $v) {
      $fieldName = $v["db_field_name"];
      if (in_array($fieldName, $ignorFields)) {
        continue;
      }

      $result[] = [
        "id" => $v["id"],
        "caption" => $v["caption"],
        "fieldName" => $fieldName,
        "colWidth" => $v["col_width"],
      ];
    }

    return $result;
  }

  /**
   * 查询明细账数据
   */
  public function queryDataForDetailAcc($params)
  {
    $db = $this->db;

    $companyId = $params["companyId"];
    $subjectCode = $params["subjectCode"];
    $year = $params["year"];
    $month = $params["month"];

    $subAccLevel = $params["subAccLevel"];

    // 查询物料小数位数
    $bcDAO = new BizConfigDAO($db);
    $dataScale = $bcDAO->getGoodsCountDecNumber($companyId);
    $fmt = "decimal(19, " . $dataScale . ")";

    $sql = "select id, db_table_name_prefix
            from t_acc_fmt
            where company_id = '%s' and subject_code = '%s' ";
    $data = $db->query($sql, $companyId, $subjectCode);
    if (!$data) {
      return $this->emptyResult();
    }
    $v = $data[0];
    $fmtId = $v["id"];
    $accTableName = $v["db_table_name_prefix"] . "_detail";

    // 查询子账簿层级字段
    // 目前的设计是：最多有三个层级字段
    $sql = "select db_field_name, voucher_input
              from t_acc_fmt_cols
              where fmt_id = '%s' and sub_acc_level > 0 and sub_acc_level < 4
              order by sub_acc_level
              limit 3";
    $dataSub = $db->query($sql, $fmtId);
    $subLevelFields = [];
    foreach ($dataSub as $vSub) {
      $subLevelFields[] = [
        "fieldName" => $vSub["db_field_name"],
        "voucherInput" => $vSub["voucher_input"],
      ];
    }

    $listCntField = ["x_db_cnt", "x_cr_cnt", "x_balance_cnt"];

    $sql = "select db_field_name
            from t_acc_fmt_cols
            where fmt_id = '%s' and col_category != 1 
            order by show_order";
    $data = $db->query($sql, $fmtId);
    $sqlForData = "select ";
    foreach ($data as $i => $v) {
      $f = $v["db_field_name"];
      if ($i > 0) {
        $sqlForData .= ",";
      }
      if (in_array($f, $listCntField)) {
        // 数量金额三栏账中的数量格式化成相应的小数位数
        $sqlForData .= "convert({$f}, " . $fmt . ") as {$f}";
      } else {
        $sqlForData .= $f;
      }
    }
    $sqlForData .= " from {$accTableName}";
    $sqlForData .= " where voucher_year = %d and voucher_month= %d";
    $sqlForData .= " and company_id = '%s' and sub_acc_level = %d";
    if ($subLevelFields) {
      $sqlForData .= " order by ";
      foreach ($subLevelFields as $idx => $slf) {
        $f = $slf["fieldName"];
        if ($slf["voucherInput"] == 2) {
          $f .= "_id";
        }
        if ($idx > 0) {
          $sqlForData .= ",";
        }
        $sqlForData .= "{$f}";
      }
      $sqlForData .= ", id";
    } else {
      $sqlForData .= " order by id";
    }

    $data = $db->query($sqlForData, $year, $month, $companyId, $subAccLevel);

    return $data;
  }

  /**
   * 查询一级科目列表
   */
  public function subjectList($params)
  {
    $db = $this->db;

    $companyId = $params["companyId"];
    $subjectCode = $params["subjectCode"];

    $sql = "select code, name
            from t_subject
            where company_id = '%s' and parent_id is null and inited = 1 ";
    $queryParams = [];
    $queryParams[] = $companyId;
    if ($subjectCode) {
      $sql .= " and code like '%s' ";
      $queryParams[] = "{$subjectCode}%";
    }
    $sql .= " order by code";
    $data = $db->query($sql, $queryParams);

    $result = [];

    foreach ($data as $v) {
      $result[] = [
        "code" => $v["code"],
        "name" => $v["name"],
      ];
    }
    return $result;
  }

  /**
   * 查询总分类账数据
   */
  public function queryAccGlData($params)
  {
    $db = $this->db;

    $companyId = $params["companyId"];
    $subjectCode = $params["subjectCode"];
    $year = $params["year"];
    $month = $params["month"];

    $sql = "select gl_table_name, name
            from t_subject
            where company_id = '%s' and code = '%s' ";

    $data = $db->query($sql, $companyId, $subjectCode);
    if (!$data) {
      // 莫名的bug
      return [];
    }

    $subjectName = $data[0]["name"];
    $tableName = $data[0]["gl_table_name"];

    $sql = "select voucher_dt, acc_db, acc_cr, acc_balance, acc_balance_dbcr, voucher_summary
            from {$tableName}
            where voucher_year = %d and voucher_month = %d
              and company_id = '%s'
            order by id";
    $data = $db->query($sql, $year, $month, $companyId);

    $result = [];

    foreach ($data as $v) {
      $result[] = [
        "code" => $subjectCode,
        "name" => $subjectName,
        "voucherDT" => $v["voucher_dt"],
        "debit" => $v["acc_db"],
        "credit" => $v["acc_cr"],
        "balance" => $v["acc_balance"],
        "balanceDir" => $v["acc_balance_dbcr"],
        "voucherSummary" => $v["voucher_summary"],
      ];
    }

    return $result;
  }

  /**
   * 登记总账
   */
  public function keepGl(&$params)
  {
    $db = $this->db;

    $companyId = $params["companyId"];
    $loginUserId = $params["loginUserId"];
    $summaryExt = $params["summaryExt"];

    $sql = "select name from t_user where id = '%s'";
    $data = $db->query($sql, $loginUserId);
    if (!$data) {
      return $this->badParam("loginUserId");
    }
    $loginUserName = $data[0]["name"];

    $sql = "select name
            from t_org
            where id = '%s' and parent_id is null";
    $data = $db->query($sql, $companyId);
    if (!$data) {
      return $this->badParam("companyId");
    }
    $companyName = $data[0]["name"];

    $daoComm = new AccCommDAO($db);
    $r = $daoComm->getCurrentAccYearAndMonth($companyId);
    if (!$r) {
      return $this->bad("{$companyName}没有初始会计期间");
    }
    $accYear = $r["accYear"];
    $accMonth = $r["accMonth"];
    $p = $daoComm->buildPeriodStr($accYear, $accMonth);

    // 检查明细账是否已经记账
    // 明细账没有记账，则不能记总账
    $sql = "select acc_detail_closed
            from t_acc_period
            where company_id = '%s' and acc_year = %d and acc_month = %d";
    $data = $db->query($sql, $companyId, $accYear, $accMonth);
    if (!$data) {
      // 莫名的bug
      return $this->bad("{$companyName}没有初始化会计期间");
    }
    $detailClosed = $data[0]["acc_detail_closed"] == 1;
    if (!$detailClosed) {
      return $this->bad("{$companyName}会计期间{$p}的明细分类账还没有关账，不能登记总分类账");
    }

    // 按一级科目依次汇总总账
    $sql = "select code, gl_table_name
            from t_subject
            where company_id = '%s' and parent_id is null
            order by code";
    $data = $db->query($sql, $companyId);
    foreach ($data as $v) {
      $subjectCode = $v["code"];
      $tableName = $v["gl_table_name"];

      $rc = $this->sumByTopSubjectCodeForGl(
        $companyId,
        $accYear,
        $accMonth,
        $subjectCode,
        $tableName,
        $loginUserId,
        $loginUserName,
        $summaryExt,
      );
      if ($rc) {
        return $rc;
      }
    }

    // 在凭证上标记已经登记总账
    $sql = "update t_voucher
            set gl_dt_2 = now(), gl_user_id_2 = '%s', gl_user_name_2 = '%s',
              v_status = 3000
            where company_id = '%s' and v_year = %d and v_month = %d
              and gl_dt_2 is null";
    $rc = $db->execute($sql, $loginUserId, $loginUserName, $companyId, $accYear, $accMonth);
    if ($rc === false) {
      return $this->sqlError(__METHOD__, __LINE__);
    }

    // 业务日志
    $log = "[{$companyName}] - 登记会计期间{$p}的总分类账";
    $params["log"] = $log;

    // 操作成功
    return null;
  }

  /**
   * 对某个科目汇总总账
   */
  private function sumByTopSubjectCodeForGl(
    $companyId,
    $accYear,
    $accMonth,
    $subjectCode,
    $tableName,
    $loginUserId,
    $loginUserName,
    $summaryExt
  ) {
    $db = $this->db;
    $daoComm = new AccCommDAO($db);

    // 记账的日期
    $voucherDT = $daoComm->buildVoucherLastDT($accYear, $accMonth);

    $sql = "select count(*) as cnt
            from t_voucher v, t_voucher_detail vd
            where v.company_id = '%s' 
              and v.gl_dt_2 is null and v.v_year = %d and v.v_month = %d
              and v.id = vd.voucher_id and vd.subject = '%s' ";
    $data = $db->query($sql, $companyId, $accYear, $accMonth, $subjectCode);
    $cnt = $data[0]["cnt"];

    // 没有需要汇总的数据
    if ($cnt === 0) {
      return null;
    }

    $dirName = $daoComm->querySubjectDir($companyId, $subjectCode);
    if (!$dirName) {
      // 莫名的bug
      return $this->badParam("subjectCode");
    }

    $sql = "select ifnull(sum(vd.debit),0) as debit, ifnull(sum(vd.credit),0) as credit,
              (ifnull(sum(vd.debit),0) - ifnull(sum(vd.credit),0)) as balance
            from t_voucher v, t_voucher_detail vd
            where v.id = vd.voucher_id and v.company_id = '%s' 
              and v.gl_dt_2 is null and v.v_year = %d and v.v_month = %d
              and vd.subject = '%s' ";
    $data = $db->query($sql, $companyId, $accYear, $accMonth, $subjectCode);
    $v = $data[0];
    $accBalance = $v["balance"];
    $accDb = $v["debit"];
    $accCr = $v["credit"];

    $now = date("Y-m-d H:i:s");

    // 查询当前的余额
    $sql = "select acc_balance, acc_balance_dbcr
            from {$tableName}
            where company_id = '%s' and voucher_year = %d and voucher_month = %d
            order by id desc
            limit 1";
    $data = $db->query($sql, $companyId, $accYear, $accMonth);
    $lastBalance = 0;
    if ($data) {
      $v = $data[0];
      $lastBalance = $v["acc_balance"];
      $lastDirName = $v["acc_balance_dbcr"];
    }

    if ($dirName == "借方") {
      $lastBalance += $accBalance;
    } elseif ($dirName == "贷方") {
      $lastBalance -= $accBalance;
    } elseif ($dirName == "借贷均可") {
      // 例如： 科目4103本年利润，余额是借贷均可，4103余额在借方表示亏损，余额在贷方表示盈利
      if ($lastDirName == "借方") {
        $lastBalance = -$lastBalance;
      }
      $lastBalance -= $accBalance;
      if ($lastBalance < 0) {
        $lastBalance = -$lastBalance;
        $dirName = "借方";
      } else {
        $dirName = "贷方";
      }
    }

    $summary = "于{$now}登记总分类账";
    if ($summaryExt) {
      $summary = "{$summaryExt}, {$summary}";
    }

    $queryParams = [];
    $sql = "insert into {$tableName}
              (acc_id, company_id, acc_user_id, biz_user_id, subject_code, 
                voucher_dt, voucher_year, voucher_month, acc_user_name,
                biz_user_name, acc_db, acc_cr, acc_balance, acc_balance_dbcr,
                voucher_summary)
              values
              ('%s', '%s', '%s', '%s', '%s',
              '%s', %d, %d, '%s',
              '%s', if(abs(%f)<0.000001, null, %f), if(abs(%f)<0.000001, null, %f), %f, '%s', 
              '{$summary}')";
    $queryParams[] = $this->newId();
    $queryParams[] = $companyId;
    $queryParams[] = $loginUserId;
    $queryParams[] = $loginUserId;
    $queryParams[] = $subjectCode;
    $queryParams[] = $voucherDT;
    $queryParams[] = $accYear;
    $queryParams[] = $accMonth;
    $queryParams[] = $loginUserName;
    $queryParams[] = $loginUserName;

    // 借贷余
    $queryParams[] = $accDb;
    $queryParams[] = $accDb;
    $queryParams[] = $accCr;
    $queryParams[] = $accCr;
    $queryParams[] = $lastBalance;

    $queryParams[] = $dirName;

    $rc = $db->execute($sql, $queryParams);
    if ($rc === false) {
      return $this->sqlError(__METHOD__, __LINE__);
    }

    // 操作成功
    return null;
  }

  /**
   * 关闭明细账
   */
  public function closeAccDetail(&$params)
  {
    $db = $this->db;

    $companyId = $params["companyId"];
    $sql = "select name
            from t_org where id = '%s' and parent_id is null ";
    $data = $db->query($sql, $companyId);
    if (!$data) {
      return $this->badParam("companyId");
    }

    $companyName = $data[0]["name"];

    $daoComm = new AccCommDAO($db);
    $r = $daoComm->getCurrentAccYearAndMonth($companyId);
    if (!$r) {
      return $this->bad("{$companyName}没有建账");
    }
    $accYear = $r["accYear"];
    $accMonth = $r["accMonth"];
    $p = "";
    if ($accMonth < 10) {
      $p = "{$accYear}-0{$accMonth}";
    } else {
      $p = "{$accYear}-{$accMonth}";
    }

    $serverYear = intval(date("Y"));
    $serverMonth = intval(date("m"));
    if (intval($accYear) > $serverYear) {
      // 莫名的bug
      return $this->bad("当前会计期间年份大于当前服务器时间的年份，不能关账");
    }
    if (intval($accYear) == $serverYear) {
      if (intval($accMonth) > $serverMonth) {
        return $this->bad("当前会计期间月份大于当前服务器时间的月份，不能关账");
      }
    }

    $sql = "select acc_detail_closed
            from t_acc_period
            where company_id = '%s' and acc_year = %d and acc_month = %d";
    $data = $db->query($sql, $companyId, $accYear, $accMonth);
    if (!$data) {
      return $this->bad("{$companyName}没有初始会计期间");
    }
    $closed = $data[0]["acc_detail_closed"] == 1;
    if ($closed) {
      return $this->bad("[{$companyName}]会计期间[{$p}]的明细分类账已经关闭");
    }

    // 检查是否还有凭证未记账
    $sql = "select count(*) as cnt
            from t_voucher
            where company_id = '%s' and v_year = %d and v_month = %d
              and gl_dt is null";
    $data = $db->query($sql, $companyId, $accYear, $accMonth);
    $cnt = $data[0]["cnt"];
    if ($cnt > 0) {
      return $this->bad("[{$companyName}]会计期间[{$p}]还有凭证没有登记明细分类账");
    }

    $sql = "update t_acc_period
            set acc_detail_closed = 1
            where company_id = '%s' and acc_year = %d and acc_month = %d";
    $rc = $db->execute($sql, $companyId, $accYear, $accMonth);
    if ($rc === false) {
      return $this->sqlError(__METHOD__, __LINE__);
    }

    // 业务日志
    $log = "[$companyName] - 关闭会计期间[{$p}]的明细分类账";
    $params["log"] = $log;

    // 操作成功
    return null;
  }

  /**
   * 取消关闭明细账
   */
  public function cancelCloseAccDetail(&$params)
  {
    $db = $this->db;

    $companyId = $params["companyId"];
    $sql = "select name
            from t_org where id = '%s' and parent_id is null ";
    $data = $db->query($sql, $companyId);
    if (!$data) {
      return $this->badParam("companyId");
    }

    $companyName = $data[0]["name"];

    $daoComm = new AccCommDAO($db);
    $r = $daoComm->getCurrentAccYearAndMonth($companyId);
    if (!$r) {
      return $this->bad("{$companyName}没有建账");
    }
    $accYear = $r["accYear"];
    $accMonth = $r["accMonth"];
    $p = "";
    if ($accMonth < 10) {
      $p = "{$accYear}-0{$accMonth}";
    } else {
      $p = "{$accYear}-{$accMonth}";
    }

    $sql = "select acc_detail_closed, acc_gl_closed
            from t_acc_period
            where company_id = '%s' and acc_year = %d and acc_month = %d";
    $data = $db->query($sql, $companyId, $accYear, $accMonth);
    if (!$data) {
      return $this->bad("{$companyName}没有初始会计期间");
    }
    $detailClosed = $data[0]["acc_detail_closed"] == 1;
    $glClosed = $data[0]["acc_gl_closed"] == 1;

    if (!$detailClosed) {
      return $this->bad("[{$companyName}]会计期间[{$p}]的明细分类账没有关闭，无须进行取消操作");
    }

    if ($glClosed) {
      return $this->bad("[{$companyName}]会计期间[{$p}]的总分类账已经关闭，无法取消明细分类账的关账标志");
    }

    $sql = "update t_acc_period
            set acc_detail_closed = 0
            where company_id = '%s' and acc_year = %d and acc_month = %d";
    $rc = $db->execute($sql, $companyId, $accYear, $accMonth);
    if ($rc === false) {
      return $this->sqlError(__METHOD__, __LINE__);
    }

    // 业务日志
    $log = "[$companyName] - 取消关闭会计期间[{$p}]的明细分类账";
    $params["log"] = $log;

    // 操作成功
    return null;
  }

  /**
   * 关闭总账
   */
  public function closeAccGL(&$params)
  {
    $db = $this->db;

    $companyId = $params["companyId"];
    $sql = "select name
            from t_org where id = '%s' and parent_id is null ";
    $data = $db->query($sql, $companyId);
    if (!$data) {
      return $this->badParam("companyId");
    }

    $companyName = $data[0]["name"];
    $daoComm = new AccCommDAO($db);
    $r = $daoComm->getCurrentAccYearAndMonth($companyId);
    if (!$r) {
      return $this->bad("{$companyName}没有建账");
    }
    $accYear = $r["accYear"];
    $accMonth = $r["accMonth"];
    $p = "";
    if ($accMonth < 10) {
      $p = "{$accYear}-0{$accMonth}";
    } else {
      $p = "{$accYear}-{$accMonth}";
    }

    $sql = "select acc_detail_closed, acc_gl_closed
            from t_acc_period
            where company_id = '%s' and acc_year = %d and acc_month = %d";
    $data = $db->query($sql, $companyId, $accYear, $accMonth);
    if (!$data) {
      return $this->bad("{$companyName}没有初始会计期间");
    }
    $detailClosed = $data[0]["acc_detail_closed"] == 1;
    $glClosed = $data[0]["acc_gl_closed"] == 1;
    if (!$detailClosed) {
      return $this->bad("[{$companyName}]会计期间[{$p}]的明细分类账还没有关闭之前，总账不能关闭");
    }
    if ($glClosed) {
      return $this->bad("[{$companyName}]会计期间[{$p}]的总分类账已经关闭");
    }

    // 检查是否还有凭证未记账
    $sql = "select count(*) as cnt
            from t_voucher
            where company_id = '%s' and v_year = %d and v_month = %d
              and gl_dt_2 is null";
    $data = $db->query($sql, $companyId, $accYear, $accMonth);
    $cnt = $data[0]["cnt"];
    if ($cnt > 0) {
      return $this->bad("[{$companyName}]会计期间[{$p}]还有凭证没有登记总分类账");
    }

    $sql = "update t_acc_period
            set acc_gl_closed = 1
            where company_id = '%s' and acc_year = %d and acc_month = %d";
    $rc = $db->execute($sql, $companyId, $accYear, $accMonth);
    if ($rc === false) {
      return $this->sqlError(__METHOD__, __LINE__);
    }

    // 业务日志
    $log = "[$companyName] - 关闭会计期间[{$p}]的总分类账";
    $params["log"] = $log;

    // 操作成功
    return null;
  }

  /**
   * 取消关闭总账
   */
  public function cancelCloseAccGL(&$params)
  {
    $db = $this->db;

    $companyId = $params["companyId"];
    $sql = "select name
            from t_org where id = '%s' and parent_id is null ";
    $data = $db->query($sql, $companyId);
    if (!$data) {
      return $this->badParam("companyId");
    }

    $companyName = $data[0]["name"];

    $daoComm = new AccCommDAO($db);
    $r = $daoComm->getCurrentAccYearAndMonth($companyId);
    if (!$r) {
      return $this->bad("{$companyName}没有建账");
    }
    $accYear = $r["accYear"];
    $accMonth = $r["accMonth"];
    $p = "";
    if ($accMonth < 10) {
      $p = "{$accYear}-0{$accMonth}";
    } else {
      $p = "{$accYear}-{$accMonth}";
    }
    $sql = "select acc_gl_closed, period_closed
            from t_acc_period
            where company_id = '%s' and acc_year = %d and acc_month = %d";
    $data = $db->query($sql, $companyId, $accYear, $accMonth);
    if (!$data) {
      return $this->bad("{$companyName}没有初始会计期间");
    }
    $glClosed = $data[0]["acc_gl_closed"] == 1;
    $periodClosed = $data[0]["period_closed"] == 1;

    if (!$glClosed) {
      return $this->bad("[{$companyName}]会计期间[{$p}]的总分类账还没有关账，无须取消关账");
    }
    if ($periodClosed) {
      $info = "[{$companyName}]会计期间[{$p}]已经结账，不能取消总账关账";
      return $this->bad($info);
    }

    $sql = "update t_acc_period
            set acc_gl_closed = 0
            where company_id = '%s' and acc_year = %d and acc_month = %d";
    $rc = $db->execute($sql, $companyId, $accYear, $accMonth);
    if ($rc === false) {
      return $this->sqlError(__METHOD__, __LINE__);
    }

    // 业务日志
    $log = "[$companyName] - 取消关闭会计期间[{$p}]的总分类账";
    $params["log"] = $log;

    // 操作成功
    return null;
  }
}
